﻿using DapperExtensions;
using DapperExtensions.Sql;
using DotNet.Http.Core;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using webapiproxy;

namespace FXPushService
{
    public class DataFetcher
    {
        public class ApiResult
        {
            public bool success { get; set; }

            public string msg { get; set; }

            public object data { get; set; }
        }

        IDbConnection GetDBConn()
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString);
            conn.Open();
            return conn;
        }
        /// <summary>  
        /// 批量插入功能  
        /// </summary>  
        public void InsertBatch<T>(IDbConnection conn, IEnumerable<T> entityList, IDbTransaction transaction = null) where T : class
        {
            var tblName = string.Format("dbo.{0}", typeof(T).Name);
            var tran = (SqlTransaction)transaction;
            using (var bulkCopy = new SqlBulkCopy(conn as SqlConnection, SqlBulkCopyOptions.TableLock, tran))
            {
                bulkCopy.BatchSize = entityList.Count();
                bulkCopy.DestinationTableName = tblName;
                var table = new DataTable();
                DapperExtensions.Sql.ISqlGenerator sqlGenerator = new SqlGeneratorImpl(new DapperExtensionsConfiguration());
                var classMap = sqlGenerator.Configuration.GetMap<T>();
                var props = classMap.Properties.Where(x => x.Ignored == false).ToArray();
                foreach (var propertyInfo in props)
                {
                    bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
                    table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyInfo.PropertyType) ?? propertyInfo.PropertyInfo.PropertyType);
                }
                var values = new object[props.Count()];
                foreach (var itemm in entityList)
                {
                    for (var i = 0; i < values.Length; i++)
                    {
                        values[i] = props[i].PropertyInfo.GetValue(itemm, null);
                    }
                    table.Rows.Add(values);
                }
                bulkCopy.WriteToServer(table);
            }
        }

        public ApiResult GetRiver()
        {
            ApiResult result = new ApiResult();
            HttpResponseParameter responseParameter1 = HttpUtil.Excute(new HttpRequestParameter
            {
                Url = "http://10.21.5.211:8080/lnflood/app/hydro/riverinfo",
                IsPost = false,
                Encoding = Encoding.UTF8

            });
            result.success = true;
            try
            {
                var resarray = JsonConvert.DeserializeObject<List<river>>(responseParameter1.Body);
                var temp = resarray.Where(o =>
                {
                    if (string.IsNullOrEmpty(o.addvcd)) return false;
                    return o.addvcd.StartsWith("2111");
                });

              
                var conn = GetDBConn();
                Model1 db = new Model1();
                //InsertBatch<river>(conn, temp.ToList());
                foreach (var t in temp)
                {
                    int count= db.reservior.Count(o => o.stcd == t.stcd);
                    //int count = Convert.ToInt32(Dapper.SqlMapper.ExecuteScalar(conn, "select count(*) from river where stcd='" + t.stcd + "'"));

                    if (count == 0)
                    {

                        
     //                   string sql = string.Format(@"INSERT INTO [dbo].[river]
     //      ([stnm]
     //      ,[tm]
     //      ,[overgrq]
     //      ,[addvcd]
     //      ,[overwrz]
     //      ,[overgrz]
     //      ,[rvnm]
     //      ,[overwrq]
     //      ,[lttd]
     //      ,[grq]
     //      ,[bsnm]
     //      ,[wrz]
     //      ,[stcd]
     //      ,[lgtd]
     //      ,[q]
     //      ,[grz]
     //      ,[wptn]
     //      ,[wrq]
     //      ,[stlc]
     //      ,[z])
     //VALUES
     //      ('{0}'
     //      ,'{1}'
     //      ,{2}
     //      ,'{3}'
     //      ,{4}
     //      ,{5}
     //      ,'{6}'
     //      ,{7}
     //      ,{8}
     //      ,{9}
     //      ,'{10}'
     //      ,'{11}'
     //      ,'{12}'
     //      ,{13}
     //      ,{14}
     //      ,{15}
     //      ,'{16}'
     //      ,{17}
     //      ,'{18}'
     //      ,{19})", t.stnm, t.tm, t.overgrq, t.addvcd, t.overwrz, t.overgrz, t.rvnm, t.overwrq, t.lttd, t.grq, t.bsnm, t.wrz, t.stcd,
     //      t.lgtd, t.q, t.grz, t.wptn, t.wrq, t.stlc, t.z);
     //                   Dapper.SqlMapper.Execute(conn, sql);

                    }
                    else
                    {
                        string sql = string.Format(@"UPDATE [dbo].[river]
   SET [stnm] ='{0}'  
      ,[tm] = '{1}'
      ,[overgrq] = {2} 
      ,[addvcd] ='{3}'  
      ,[overwrz] = {4}  
      ,[overgrz] = {5} 
      ,[rvnm] ='{6}'  
      ,[overwrq] = {7}  
      ,[lttd] = {8}  
      ,[grq] = {9} 
      ,[bsnm] ='{10}'  
      ,[wrz] = {11}  
     
      ,[lgtd] = {12} 
      ,[q] = {13} 
      ,[grz] =  {14} 
      ,[wptn] ='{15}'  
      ,[wrq] = {16} 
      ,[stlc] ='{17}'  
      ,[z] =  {18} 
 WHERE  [stcd] ='{19}'", t.stnm, t.tm, t.overgrq, t.addvcd, t.overwrz, t.overgrz, t.rvnm, t.overwrq, t.lttd, t.grq, t.bsnm,
 t.wrz, t.lgtd, t.q, t.grz, t.wptn, t.wrq, t.stlc, t.stcd);
                        Dapper.SqlMapper.Execute(conn,sql);

                    }
                }


            }
            catch (Exception ex)
            {
                result.success = false;
                Console.WriteLine(ex.Message);
            }

            return result;
        }

        public ApiResult GetResinfo()
        {
            ApiResult result = new ApiResult();
            HttpResponseParameter responseParameter1 = HttpUtil.Excute(new HttpRequestParameter
            {
                Url = "http://10.21.5.211:8080/lnflood/app/hydro/resinfo",
                IsPost = false,
                Encoding = Encoding.UTF8

            });
            result.success = true;
            try
            {
                var resarray = JsonConvert.DeserializeObject<resinfo>(responseParameter1.Body);
                var temp = resarray.items.Where(o =>
                {

                    if (string.IsNullOrEmpty(o.addvcd)) return false;
                    return o.addvcd.StartsWith("2111");
                });
                

                var conn = GetDBConn();

                //InsertBatch<reservior>(conn, temp.ToList());
                foreach (var t in temp)
                {
                    int count = Convert.ToInt32(Dapper.SqlMapper.ExecuteScalar(conn, "select count(*) from reservior where stcd='" + t.stcd + "'"));

                    if (count == 0)
                    {
                        string sql = string.Format(@"INSERT INTO [dbo].[reservior]
           ([tm]
           ,[stnm]
           ,[damel]
           ,[avotq]
           ,[lttd]
           ,[bsnm]
           ,[fsltdw]
           ,[fsltdz]
           ,[otq]
           ,[normz]
           ,[overflz]
           ,[addvcd]
           ,[inq]
           ,[ckflz]
           ,[avinq]
           ,[overnormz]
           ,[rvnm]
           ,[w]
           ,[rz]
           ,[rsvrtp]
           ,[dsflz]
           ,[stcd]
           ,[lgtd]
           ,[wptn]
           ,[overfsltdw]
           ,[stlc])
     VALUES
           ('{0}'
           ,'{1}'
           ,{2}
           ,{3}
           ,{4}
           ,'{5}'
           ,{6}
           ,{7}
           ,{8}
           ,{9}
           ,{10}
           ,'{11}'
           ,{12}
           ,{13}
           ,{14}
           ,{15}
           ,'{16}'
           ,{17}
           ,{18}
           ,{19}
           ,{20}
           ,'{21}'
           ,{22}
           ,'{23}'
           ,{24}
           ,'{25}')", t.tm, t.stnm, t.damel, t.avotq, t.lttd, t.bsnm, t.fsltdw, t.fsltdz, t.otq, t.normz, t.overflz, t.addvcd,
           t.inq, t.ckflz, t.avinq, t.overnormz, t.rvnm, t.w, t.rz, t.rsvrtp, t.dsflz, t.stcd, t.lgtd, t.wptn, t.overfsltdw, t.stlc);
                        Dapper.SqlMapper.Execute(conn,sql );
                    }
                    else
                    {
                        string sql = string.Format(@"UPDATE [dbo].[reservior]
   SET [tm] = '{0}'
      ,[stnm] = '{1}'
      ,[damel] = {2}
      ,[avotq] = {3}
      ,[lttd] = {4}
      ,[bsnm] = '{5}'
      ,[fsltdw] = {6}
      ,[fsltdz] = {7}
      ,[otq] = {8}
      ,[normz] = {9}
      ,[overflz] = {10}
      ,[addvcd] = '{11}'
      ,[inq] = {12}
      ,[ckflz] ={13}
      ,[avinq] = {14}
      ,[overnormz] = {15}
      ,[rvnm] ='{16}'
      ,[w] ={17}
      ,[rz] = {18}
      ,[rsvrtp] = {19}
      ,[dsflz] = {20}
      ,[lgtd] = '{21}'
      ,[wptn] = {22}
      ,[overfsltdw] = '{23}'
      ,[stlc] = {24}
 WHERE  [stcd] = '{25}'", t.tm, t.stnm, t.damel, t.avotq, t.lttd, t.bsnm, t.fsltdw, t.fsltdz, t.otq, t.normz, t.overflz, t.addvcd,
           t.inq, t.ckflz, t.avinq, t.overnormz, t.rvnm, t.w, t.rz, t.rsvrtp, t.dsflz, t.lgtd, t.wptn, t.overfsltdw, t.stlc, t.stcd);
                        Dapper.SqlMapper.Execute(conn,sql);
                    }
                }


            }
            catch (Exception ex)
            {
                result.success = false;
                Console.WriteLine(ex.Message);
            }

            return result;
        }

        public ApiResult GetDayRain()
        {
            ApiResult result = new ApiResult();
            HttpResponseParameter responseParameter1 = HttpUtil.Excute(new HttpRequestParameter
            {
                Url = "http://10.21.5.211:8080/lnflood/app/hydro/dayrain",
                IsPost = false,
                Encoding = Encoding.UTF8

            });
            result.success = true;
            try
            {
                var resarray = JsonConvert.DeserializeObject<raininfo>(responseParameter1.Body);
                var temp = resarray.data.Where(o =>
                {

                    if (string.IsNullOrEmpty(o.addvcd)) return false;
                    return o.addvcd.StartsWith("2111");
                });
              
                var conn = GetDBConn();

                //InsertBatch<dayrain>(conn, temp.ToList());
                foreach (var t in temp)
                {
                    int count= Convert.ToInt32( Dapper.SqlMapper.ExecuteScalar(conn, "select count(*) from dayrain where stcd='"+t.stcd+"'"));

                    if (count == 0)
                    {
                        Dapper.SqlMapper.Execute(conn, string.Format(@"INSERT INTO [dbo].[dayrain]
           ([stnm]
           ,[addvcd]
           ,[stm]
           ,[etm]
           ,[drp]
           ,[lttd]
           ,[lgtd]
           ,[stlc]
           ,[frgrd]
           ,[stcd])
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,{4}
           ,{5}
           ,{6}
           ,'{7}'
           ,{8}
           ,'{9}')",t.stnm,t.addvcd,t.stm,t.etm,t.drp,t.lttd,t.lgtd,t.stlc,t.frgrd,t.stcd));

                    }
                    else
                    {
                        Dapper.SqlMapper.Execute(conn, string.Format(@"update [dbo].[dayrain]
           set [stnm]='{0}'
           ,[addvcd]='{1}'
           ,[stm]='{2}'
           ,[etm]='{3}'
           ,[drp]={4}
           ,[lttd]={5}
           ,[lgtd]={6}
           ,[stlc]='{7}'
           ,[frgrd]={8}
           where [stcd] ='{9}'", t.stnm, t.addvcd, t.stm, t.etm, t.drp, t.lttd, t.lgtd, t.stlc, t.frgrd, t.stcd));

                    }
                }

            }
            catch (Exception ex)
            {
                result.success = false;
                Console.WriteLine(ex.Message);
            }

            return result;
        }



    }
}
